<?php
//The next comment block is for PHPDocumentor
/**
 * Conference Registration - Registration for conferences
 * 
 * Administration functions
 * 
 * @author Kevin van Haaren (kevin@vanhaaren.net)
 * @copyright 2006 Kevin van Haaren, US
 * @package CONFREG
 * @subpackage API_Functions
 * 
 */


/* Put your admin functions into this file
 * It will automatically be included into your admin side pages
 */

function get_list_confs() {
	global $xoopsDB;

	$confHdl =& $xoopsDB->query("SELECT *" .
									" FROM " . $xoopsDB->prefix("confreg_conferences") .
									" WHERE conf_archived <> 1");
	// build an array where each row is an array of names=>values from DB
	while ($confData = $xoopsDB->fetchArray($confHdl)) {
		$conferences[] = $confData;
	}

	return $conferences;
}

function get_list_groups($conf_id) {
	global $xoopsDB;
	
	$groupHdl = $xoopsDB->query("SELECT *" .
							" FROM " . $xoopsDB->prefix("confreg_groups") .
							" WHERE conf_id = '" . intval($conf_id) . "'" .
							" ORDER BY group_id");

	while ($groupData = $xoopsDB->fetchArray($groupHdl)) {
		$groups[] = $groupData;
	}

	return $groups;
}

function get_groups_info($conf_id) {
	global $xoopsDB;
	
	$groupHdl = $xoopsDB->query("SELECT *" .
							" FROM " . $xoopsDB->prefix("confreg_groups") .
							" WHERE conf_id = '" . intval($conf_id) . "'" .
							" ORDER BY group_id");

	while ($groupData = $xoopsDB->fetchArray($groupHdl)) {
		$userHdl = $xoopsDB->query("SELECT *" .
									" FROM " . $xoopsDB->prefix("confreg_people") .
									" WHERE group_id = '" . intval($groupData['group_id']) . "'" );
		$groupData['total_count'] = 0;
		$groupData['total_cost'] = 0;
		while ($userData = $xoopsDB->fetchArray($userHdl)) {
			$groupData['total_count']++;
			$groupData['total_cost'] += $userData['cost'];			
		}
		$groups[] = $groupData;
	}
	return $groups;
}

function get_all_users($conf_id) {
	global $xoopsDB;

	$dbHdl = $xoopsDB->query("SELECT p.*, g.company, g.paid, g.group_id" .
	                        " FROM " . $xoopsDB->prefix("confreg_people") . " as p" .
	                        " JOIN " . $xoopsDB->prefix("confreg_groups") . " as g" .
	                        " ON p.group_id = g.group_id" .
	                        " WHERE (g.conf_id = " . intval($conf_id) . ")" .
	                        " ORDER BY p.last_name");

	while ($dbRec = $xoopsDB->fetchArray($dbHdl)) {
		$users[] = $dbRec;
		if (isset($group_cost[$dbRec["group_id"]])) {
			$group_cost[intval($dbRec["group_id"])] += intval($dbRec['cost']);
		} else {
			$group_cost[intval($dbRec["group_id"])] = intval($dbRec['cost']);
		}		
	}
	
	// additional info
	foreach ($users as &$thisUser) {

		$eHdl = $xoopsDB->query("SELECT e.name" .
								" FROM " . $xoopsDB->prefix("confreg_extra_reservations") . " as r" .
								" JOIN " . $xoopsDB->prefix("confreg_extras") . " as e" .
								" ON r.extra_id = e.extra_id" .
								" WHERE (r.user_id = " . intval($thisUser["user_id"]) . ")");
		$extras = "";
		while ($eRec = $xoopsDB->fetchArray($eHdl)) {
			$extras .= $eRec['name'] . ';';
		}
		$thisUser['extras'] = $extras;
		if (intval($thisUser['paid']) == intval($group_cost[intval($thisUser["group_id"])])) {
			$thisUser['paid'] = "Yes";
		} else {
			$thisUser['paid'] = "No";
		}
	}
	return $users;
}

function get_x_users($conf_id,$start,$count) {
	global $xoopsDB;

	$dbHdl = $xoopsDB->query("SELECT p.*, g.company, g.paid, g.group_id" .
	                        " FROM " . $xoopsDB->prefix("confreg_people") . " as p" .
	                        " JOIN " . $xoopsDB->prefix("confreg_groups") . " as g" .
	                        " ON p.group_id = g.group_id" .
	                        " WHERE (g.conf_id = " . intval($conf_id) . ")" .
	                        " ORDER BY p.last_name" .
	                        " LIMIT " . intval($start).', ' . intval($count));

	while ($dbRec = $xoopsDB->fetchArray($dbHdl)) {
		$users[] = $dbRec;
		if (isset($group_cost[$dbRec["group_id"]])) {
			$group_cost[intval($dbRec["group_id"])] += intval($dbRec['cost']);
		} else {
			$group_cost[intval($dbRec["group_id"])] = intval($dbRec['cost']);
		}		
	}
	
	// additional info
	foreach ($users as &$thisUser) {

		$eHdl = $xoopsDB->query("SELECT e.name" .
								" FROM " . $xoopsDB->prefix("confreg_extra_reservations") . " as r" .
								" JOIN " . $xoopsDB->prefix("confreg_extras") . " as e" .
								" ON r.extra_id = e.extra_id" .
								" WHERE (r.user_id = " . intval($thisUser["user_id"]) . ")");
		$extras = "";
		while ($eRec = $xoopsDB->fetchArray($eHdl)) {
			$extras .= $eRec['name'] . ';';
		}
		$thisUser['extras'] = $extras;
		if (intval($thisUser['paid']) == intval($group_cost[intval($thisUser["group_id"])])) {
			$thisUser['paid'] = "Yes";
		} else {
			$thisUser['paid'] = "No";
		}
	}
	return $users;
}


function get_group_users($groupid) {
	global $xoopsDB;

	$groupid = intval($groupid);

	$uHdl =& $xoopsDB->query("SELECT * FROM " . $xoopsDB->prefix("confreg_people") .
							" WHERE (groupid = '" . addslashes($groupid) . "')");

	// build an array where each row is an array of names=>values from DB
	while ($uData = $xoopsDB->fetchArray($uHdl)) {
		$users[] = $uData;
	}

	return $users;
}

// function get_group_data($groupid) {
// 	global $xoopsDB;
// 
// 	$groupid = intval($groupid);
// 
// 	$gHdl =  $xoopsDB->query("SELECT * FROM " . $xoopsDB->prefix("confreg_groups") .
// 							" WHERE (groupid = '" . addslashes($groupid) . "')");
// 
// 	// build an array of the group data from the database
// 	$gData = $xoopsDB->fetchArray($gHdl);
// 	return $gData;
// }

function get_workshop_info($conf_id) {
	global $xoopsDB;
	
	$exHndl = $xoopsDB->query("SELECT *" .
							" FROM " . $xoopsDB->prefix("confreg_extras") .
							" WHERE ((conf_id = '" . intval($conf_id) . "')" .
					 	    " AND (start_date <> end_date))" .
							" ORDER BY start_date");

	while ($wshopData = $xoopsDB->fetchArray($exHndl)) {
		$erHndl = $xoopsDB->query("SELECT *" .
								  " FROM " . $xoopsDB->prefix("confreg_extra_reservations") .
								  " WHERE extra_id = '" . intval($wshopData['extra_id']) . "'" );
		$wshopData['count'] = 0;
		$wshopData['attendee'] = '';
		while ($userData = $xoopsDB->fetchArray($erHndl)) {
			$uHndl = $xoopsDB->query("SELECT first_name, last_name, user_id" .
								  " FROM " . $xoopsDB->prefix("confreg_people") .
								  " WHERE user_id = '" . intval($userData['user_id']) . "'" );
			$names = $xoopsDB->fetchArray($uHndl);
			if (isset($names['user_id'])) {
				$wshopData['count']++;
				$wshopData['attendee'][] = $names['first_name']. ' ' . $names['last_name'];
			}
		}
		$workshops[] = $wshopData;
	}
	return $workshops;
}

function get_extra_info($conf_id) {
	global $xoopsDB;
	
	$exHndl = $xoopsDB->query("SELECT *" .
							" FROM " . $xoopsDB->prefix("confreg_extras") .
							" WHERE ((conf_id = '" . intval($conf_id) . "')" .
					 	    " AND (start_date = end_date))" .
							" ORDER BY name");

	while ($exData = $xoopsDB->fetchArray($exHndl)) {
		$erHndl = $xoopsDB->query("SELECT *" .
								  " FROM " . $xoopsDB->prefix("confreg_extra_reservations") .
								  " WHERE extra_id = '" . intval($exData['extra_id']) . "'" );
		$exData['count'] = 0;
		$exData['attendee'] = '';
		while ($userData = $xoopsDB->fetchArray($erHndl)) {
			$uHndl = $xoopsDB->query("SELECT first_name, last_name, user_id" .
								  " FROM " . $xoopsDB->prefix("confreg_people") .
								  " WHERE user_id = '" . intval($userData['user_id']) . "'" );
			$names = $xoopsDB->fetchArray($uHndl);
			if (isset($names['user_id'])) {
				$exData['count']++;
				$exData['attendee'][] = $names['first_name']. ' ' . $names['last_name'] . '(' . $names['user_id'].')';
			}
		}
		$extras[] = $exData;
	}
	return $extras;
}

function get_coupon_info($conf_id) {
	global $xoopsDB;

	$Hndl = $xoopsDB->query("SELECT *" .
							" FROM " . $xoopsDB->prefix("confreg_coupons") .
							" WHERE (conf_id = '" . intval($conf_id) . "')" .
							" ORDER BY code");

	while ($coup = $xoopsDB->fetchArray($Hndl)) {
		$uHndl = $xoopsDB->query("SELECT *" .
								  " FROM " . $xoopsDB->prefix("confreg_people") .
								  " WHERE (strcmp(tmc_num,'" . strtolower($coup['code']) . "') = 0)" );
		$coup['count'] = 0;
		$coup['attendee'] = '';
		while ($user = $xoopsDB->fetchArray($uHndl)) {
			$coup['count']++;
			$coup['attendee'][] = $user['first_name']. ' ' . $user['last_name'];			
		}
		$coupons[] = $coup;
	}
	return $coupons;
}


function generate_reports($conf_id) {
	global $xoopsDB;
	$groups = get_groups_info($conf_id);

	$html_output = '<h2>Summary Report</h2><p><h3>Group Information</h3>'. "\n" . '<table><tr><th style="text-align:center;">Company</th><th width=5%>Attendees</th><th>Cost</th><th>Paid</th></tr>' . "\n";
	if (count($groups) > 0) {
		foreach ($groups as $thisGroup) {
			$html_output .= "<tr>";
			$html_output .= '<td>' . $thisGroup['company'] . '</td>';
			$html_output .= '<td style="text-align:center;">' . $thisGroup['total_count'] . '</td>';
			$html_output .= '<td style="text-align:right;">$' . money_format('%.2n',$thisGroup['total_cost']) . '</td>';
			$html_output .= '<td style="text-align:right;">$' . money_format('%.2n',$thisGroup['paid']) . '</td>';
			$html_output .= "</tr>\n";
		}
	} else {
			$html_output .= "<tr><td colspan=5>No groups defined yet.</td></tr>";
	}

	$html_output .= "</table>\n<h3>Coupons</h3>\n";
 	$coupons = get_coupon_info($conf_id);

 	if (count($coupons) > 0) {
 		$cell = '<ul>';
 		foreach ($coupons as $coup) {
 			$cell .= '<li style="font-weight:bold; color: #2F5376;">' . $coup['code'] . ' (' . $coup['count'] . ') - '. $coup['description'] .'</li><ul type="disc">';
 			foreach ($coup['attendee'] as $user) {
 				$cell .= '<li>' . $user . '</li>';
 			}
 			$cell .= '</ul>';
 		}
 		$cell .= '</ul>';
 		$html_output .= $cell;
 	} else {
 		$html_output .= "No coupons defined yet.";
 	}

	$html_output .= "<h3>Extra Information</h3>\n";
	$extras = get_extra_info($conf_id);
	
	if (count($extras) > 0) {
		$cell = '<ul>';
		foreach ($extras as $thisEx) {
			$cell .= '<li style="font-weight:bold; color: #2F5376;">' . $thisEx['name'] . ' (' . $thisEx['count'] . ')</li><ul type="disc">';
			foreach ($thisEx['attendee'] as $user) {
				$cell .= '<li>' . $user . '</li>';
			}
			$cell .= '</ul>';
		}
		$cell .= '</ul>';
		$html_output .= $cell;
	} else {
		$html_output .= "No extras defined yet.";
	}

	$html_output .= "\n<h3>Workshop Information</h3>\n";
	$workshops = get_workshop_info($conf_id);
	
	// break data up into left column and right column
	$left_table = '<ul type="square">';
	$right_table = '<ul type="square">';

	$left = 1;
	if (count($workshops) > 0) {
		foreach ($workshops as $wshop) {
			$cell = '<li style="font-weight:bold; color: #2F5376;">' . $wshop['name'] . ' (' . $wshop['count'] . '/' . $wshop['max_attend'] . ')</li><ul type="disc">';
			foreach ($wshop['attendee'] as $user) {
				$cell .= '<li>' . $user . '</li>';
			}
			$cell .= '</ul>';
			if ($left == 1) {
				$left_table .= $cell;
			} else {
				$right_table .= $cell;
			}
			// toggle $left between 1 and zero
			$left = 1 - $left;
		}
		$left_table .= '</ul>';
		$right_table .= '</ul>';
		$html_output .= '<table><tr><td align="left" valign="top">' . $left_table . '</td><td align="left" valign="top">' . $right_table . '</td></tr></table>';		
	} else {
		$html_output .= "No workshops defined yet.";
	}

	return $html_output;
}

function html_user_report($conf_id) {
	global $xoopsDB;

	$users = get_all_users($conf_id);

	$html_output = '<h2>Conference Reports</h2><p><h3>User Report</h3>'. "\n" . '<table><tr><th style="text-align:center;">Company</th><th>First Name</th><th>Last Name</th><th>TMC Number</th><th>Cost</th><th>Paid</th></tr>' . "\n";

	foreach ($users as $uRec) {
		$html_output .= "<tr>";
		$html_output .= '<td style="text-align:left;">' . $uRec['company'] . '</td>';
		$html_output .= '<td style="text-align:left;">' . $uRec['first_name'] . '</td>';
		$html_output .= '<td style="text-align:left;">' . $uRec['last_name'] . '</td>';
		$html_output .= '<td style="text-align:left;">' . $uRec['tmc_num'] . '</td>';
		$html_output .= '<td style="text-align:left;">' . $uRec['cost'] . '</td>';
		$html_output .= '<td style="text-align:center;">' . $uRec['paid'] . '</td>';
		$html_output .= "</tr>\n";
	}

	$html_output .= "</table>\n";

	return $html_output;
}

function get_user_extras($user_id) {
	global $xoopsDB;

	$user_id = intval($user_id);

	$rHdl = $xoopsDB->query("SELECT *" .
							" FROM " . $xoopsDB->prefix("confreg_extra_reservations") .
							" WHERE (user_id = '" . intval($user_id) . "')");

	while ($rRec = $xoopsDB->fetchArray($rHdl)) {
		$eHdl = $xoopsDB->query("SELECT *" .
								" FROM " . $xoopsDB->prefix("confreg_extras") .
								" WHERE (extra_id = '" . $rRec['extra_id'] . "')");
		$work_count = 0;
		$extra_count = 0;
		while ($eRec = $xoopsDB->fetchArray($eHdl)) {
			if ($eRec['start_date'] == $eRec['end_date']) {
				$extras['extra'][$extra_count++] = $eRec['name'];
			} else {
				$extras['workshop'][$work_count++] = $eRec['name'];
			}
		}
	}

	return $extras;
}


function send_file($filename,$content) {
	// stole this from the downloader class
	// send headers for appropriate browser
	if (preg_match("/MSIE ([0-9]\.[0-9]{1,2})/", $_SERVER['HTTP_USER_AGENT'])) {
		header('Content-Type: application/octetstream');
		header('Content-Disposition: inline; filename="' . $filename . '"');
		header('Expires: 0');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Pragma: public');
	} else {
		header('Content-Type: application/octet-stream');
		header('Content-Disposition: attachment; filename="' . $filename . '"');
		header('Expires: 0');
		header('Pragma: no-cache');
	}
	// send content
	print "$content";
}

function email_file($from,$to,$cc,$subj,$msg,$file,$file_mime) {

	// add the Mime_Mail PEAR module
	set_include_path(get_include_path() . PATH_SEPARATOR . '/home/tmcmcmc/php');
	include('Mail.php');
	include('Mail/mime.php');

	$crlf = "\n";
	$hdrs = array(
				  'From'    => $from,
				  'Cc'    => $cc,
				  'Subject' => $subj
				  );
	
	$mime = new Mail_mime($crlf);
	
	$mime->setTXTBody($msg);
	$mime->addAttachment($file, $file_mime);
	
	//do not ever try to call these lines in reverse order
	$body = $mime->get();
	$hdrs = $mime->headers($hdrs);
	
	$mail =& Mail::factory('mail');
	$result = $mail->send($to, $hdrs, $body);

	if (PEAR::isError($result)) {
		return 'error';
	} else {
		return 'success';
	}

}

function create_pdh_cert($pdf_author,$name,$date,$base_image,$file) {
	require_once('../class/tcpdf/config/lang/eng.php');
	require_once('../class/tcpdf/tcpdf.php');

	global $l;
	
	// create new PDF document
	$pdf = new TCPDF('L', PDF_UNIT, 'LETTER', true, 'UTF-8', false); 
	
	// set document information
	$pdf->SetCreator(PDF_CREATOR);
	$pdf->SetAuthor($pdf_author);
	$pdf->SetTitle('Professional Development Hours Certificate');
	$pdf->SetSubject('Professional Development Hours Certificate');
	$pdf->SetKeywords('PDH, Professional Development, Certificate');
	
	// set default header data
	$pdf->SetHeaderData('', 0, '', '');
	
	// set header and footer fonts
	$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
	$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
	
	// set default monospaced font
	$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
	
	//set margins
	$pdf->SetMargins(0, 0, 0);
	$pdf->SetHeaderMargin(0);
	$pdf->SetFooterMargin(0);
	
	//set auto page breaks
	$pdf->SetAutoPageBreak(FALSE, 0);
	
	//set image scale factor
	$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO); 
	
	//set some language-dependent strings
	$pdf->setLanguageArray($l); 
	
	// ---------------------------------------------------------
	
	// turn off headers
	$pdf->setPrintHeader(false);
	$pdf->setPrintFooter(false);
	
	// add a page
	$pdf->AddPage();
	
	// set JPEG quality
	//$pdf->setJPEGQuality(75);
	
	// add background image
	$pdf->Image($base_image, 5, 2.75, 0, 0, '', '', '', true, 300);
	
	// ---------------------------------------------------------
	
	// add name to certificate
	$pdf->SetFont('dejavusanscondensed','B',24);
	$pdf->SetXY(88,97);
	$pdf->Cell(100,0,$name,0,0,'C',0,'',0, false);
	
	// add date to certificate
	$pdf->SetFont('dejavusanscondensed','',12);
	$pdf->SetXY(19,176);
	$pdf->Cell(100,0,$date,0,0,'C',0,'',0, false);
	
	//Close and output PDF document
	$pdf->Output($file, 'F');
}

/*
 * This file was generated by XBS ModGen, (c) 2006 A Kitson, UK. See http://xoobs.net
 * ModGen is a Module Code Generator for the Xoops CMS.  See http://xoops.org
 */
?>